use std::io::BufReader;

use cross_rs::IResult;
use cross_rs::memory::bytes_io::BytesIO;
use crate::{zip::xzip, disk::file_io};


const TABLE_NAME: &str = "files";

pub fn sql_create() -> (String, String) {
    let 表名 = TABLE_NAME.to_string();
    let sql_create = format!("CREATE TABLE IF NOT EXISTS {表名} (
        id	INTEGER UNIQUE,
        年月日	INTEGER,
        文件夹	TEXT,
        文件名	TEXT,
        创建时间	INTEGER,
        修改时间	INTEGER,
        文件大小_raw	INTEGER,
        文件大小_xz	INTEGER,
        分块数量_xz	INTEGER,
        本块索引_xz	INTEGER,
        本块大小_xz	INTEGER,
        blob_xz	BLOB,
        PRIMARY KEY(id AUTOINCREMENT)
    )");
    (表名, sql_create)
}


pub struct Row_按日期存放文件 {
    pub id: i64,
    pub 年月日: i32,
    pub 文件夹: String,
    pub 文件名: String,
    pub 创建时间: i64,
    pub 修改时间: i64,
    pub 文件大小_raw: i64,
    pub 文件大小_xz: i64,

    pub 分块数量_xz: i32,
    pub 本块索引_xz: i32,
    pub 本块大小_xz: i32,
    pub blob_xz: Vec<u8>,
}
impl Row_按日期存放文件 {
    pub fn to_print(&self) -> String {
        let Self {
            id,
            年月日,
            文件夹,
            文件名,
            创建时间,
            修改时间,
            文件大小_raw,
            文件大小_xz,
            // blob_xz,
            ..
        } = self;
        format!("{id}\t{年月日}\t{文件夹}\t{文件名}\t{文件大小_raw}\t{文件大小_xz}\t{创建时间}\t{修改时间}")
    }
    pub fn sql_插入(&self) -> String {
        let Self {
            // id,
            年月日,
            文件夹,
            文件名,
            创建时间,
            修改时间,
            文件大小_raw,
            文件大小_xz,
            分块数量_xz,
            本块索引_xz,
            本块大小_xz,
            // blob_xz,
            ..
        } = self;

        let sql = format!(
            "INSERT INTO {TABLE_NAME}
 (年月日, 文件夹, 文件名, 创建时间, 修改时间, 文件大小_raw, 文件大小_xz, 分块数量_xz, 本块索引_xz, 本块大小_xz, blob_xz)
 VALUES ({年月日}, '{文件夹}', '{文件名}', {创建时间}, {修改时间}, {文件大小_raw}, {文件大小_xz}, {分块数量_xz}, {本块索引_xz}, {本块大小_xz}, ?);"
        );
        sql
    }
}

impl Row_按日期存放文件 {
    /*
    (18) SQLITE_TOOBIG
The SQLITE_TOOBIG error code indicates that a string or BLOB was too large.
The default maximum length of a string or BLOB in SQLite is 1,000,000,000 bytes.
This maximum length can be changed at compile-time using the SQLITE_MAX_LENGTH compile-time option, or at run-time using the sqlite3_limit(db,SQLITE_LIMIT_LENGTH,...) interface.
The SQLITE_TOOBIG error results when SQLite encounters a string or BLOB that exceeds the compile-time or run-time limit.

The SQLITE_TOOBIG error code can also result when an oversized SQL statement is passed into one of the sqlite3_prepare_v2() interfaces.
The maximum length of an SQL statement defaults to a much smaller value of 1,000,000,000 bytes.
The maximum SQL statement length can be set at compile-time using SQLITE_MAX_SQL_LENGTH or at run-time using sqlite3_limit(db,SQLITE_LIMIT_SQL_LENGTH,...).
     */
    pub const Mb900: usize = 900 << 20;
    pub fn split_mb900(blob: Vec<u8>) -> Vec<Vec<u8>> {
        let mb900 = Self::Mb900;
        // let mb900 = 900;
        let len = blob.len();
        if len <= mb900 {
            return vec![blob];
        }

        let mut list_blob = vec![];
        let mut start = 0;
        loop {
            let end = len.min(start + mb900);
            let sub = blob[start..end].to_vec();
            list_blob.push(sub);
            start += mb900;
            if start >= len {
                break;
            }
        }
        if list_blob.len() > 1 {
            let mut buf = format!("Row_按日期存放文件::split_mb900: {len} bytes split to {} parts:", list_blob.len());
            for blob in list_blob.iter() {
                buf.push_str(&format!(" {} +", blob.len()));
            }
            warn!("{buf}");
        }

        list_blob
    }
}

impl Row_按日期存放文件 {
    pub fn list_files(file_sqlite: &str) -> IResult<Vec<String>> {
        let conn = rusqlite::Connection::open(file_sqlite)?;
        let mut stmt = conn.prepare(&format!("SELECT 文件夹, 文件名 FROM {TABLE_NAME} WHERE 本块索引_xz=0"))?;
        let items_iter = stmt.query_map([], |row| {
            let 文件夹: String = row.get(0)?;
            let 文件名: String = row.get(1)?;
            Ok(format!("{文件夹}/{文件名}"))
            
        })?;
        let mut list_files = vec![];
        for item in items_iter {
            list_files.push(item?);
        }
        Ok(list_files)
    }

    pub fn list_items_没有blob(file_sqlite: &str) -> IResult<Vec<Self>> {
        let conn = rusqlite::Connection::open(file_sqlite)?;
        let mut stmt = conn.prepare(&format!("SELECT id, 年月日, 文件夹, 文件名, 创建时间, 修改时间, 文件大小_raw, 文件大小_xz, 分块数量_xz, 本块索引_xz, 本块大小_xz FROM {TABLE_NAME} WHERE 本块索引_xz=0"))?;
        let items_iter = stmt.query_map([], |row| {
            Ok(Self{
                id: row.get(0)?,
                年月日: row.get(1)?,
                文件夹: row.get(2)?,
                文件名: row.get(3)?,
                创建时间: row.get(4)?,
                修改时间: row.get(5)?,
                文件大小_raw: row.get(6)?,
                文件大小_xz: row.get(7)?,
                分块数量_xz: row.get(8)?,
                本块索引_xz: row.get(9)?,
                本块大小_xz: row.get(10)?,
                blob_xz: vec![],
            })
        })?;
        let mut items = vec![];
        for item in items_iter {
            items.push(item?);
        }
        Ok(items)
    }

    pub fn get_blob_xz(file_sqlite: &str, 文件夹: &str, 文件名: &str) -> IResult<Vec<u8>> {
        let conn = rusqlite::Connection::open(file_sqlite)?;
        let sql_查询 = format!("SELECT 本块索引_xz, blob_xz FROM {TABLE_NAME} WHERE 文件夹='{文件夹}' AND 文件名='{文件名}' ORDER BY 本块索引_xz");
        let mut stmt = conn.prepare(&sql_查询)?;
        let items_iter = stmt.query_map([], |row| {
            let 本块索引_xz: i32 = row.get(0)?;
            let blob_xz: Vec<u8> = row.get(1)?;
            Ok((本块索引_xz, blob_xz))
        })?;
        let mut blob = vec![];
        for item in items_iter {
            let mut sub = item?.1;
            blob.append(&mut sub);
        }
        if blob.len() == 0 {
            Err(format!("数据库中没有此文件: /{文件夹}/{文件名}, {file_sqlite}").as_str())?
        } else {
            Ok(blob)
        }
    }
    pub fn get_blob_raw(file_sqlite: &str, 文件夹: &str, 文件名: &str) -> IResult<Vec<u8>> {
        let blob_xz = Self::get_blob_xz(file_sqlite, 文件夹, 文件名)?;
        
        xzip::unzip_bytes(blob_xz)
    }

    
}

#[test]
fn test_list_items_没有blob() {
    // 2024-0104
    let file_sqlite = "X:/data/stock/static/202301/20230104.sqlite";
    let list_items_没有blob = match Row_按日期存放文件::list_items_没有blob(file_sqlite) {
        Ok(v) => v,
        Err(e) => panic!("test_list_items_没有blob: 失败！{e:?}"),
    };
    println!("============================== 共读到 {} 个文件", list_items_没有blob.len());
    for item in list_items_没有blob.iter() {
        println!("\t{}", item.to_print());
    }
}

pub fn read_utf8_lines(file_sqlite: &str, 文件夹: &str, 文件名: &str) -> Vec<String> {
    let blob_raw = match Row_按日期存放文件::get_blob_raw(&file_sqlite, 文件夹, &文件名) {
        Ok(v) => v,
        Err(e) => panic!("{e:?}"),
    };
    let len_raw = blob_raw.len();
    let lines = match BytesIO::read_utf8_lines(blob_raw) {
        Ok(v) => v,
        Err(e) => {
            error!("BytesIO::read_utf8_lines error: {e:?}");
            vec![]
        },
    };
    info!("{文件夹}/{文件名}: 原始文件大小 {}, {} 行utf8字符串", file_io::size2string(len_raw as i64), lines.len());

    lines
}

pub fn iter_utf8_lines(file_sqlite: &str, 文件夹: &str, 文件名: &str) -> std::io::Lines<BufReader<BytesIO>> {
    let blob_raw = match Row_按日期存放文件::get_blob_raw(&file_sqlite, 文件夹, &文件名) {
        Ok(v) => v,
        Err(e) => panic!("{e:?}"),
    };
    let len_raw = blob_raw.len();
    info!("{文件夹}/{文件名}: 原始文件大小 {}", file_io::size2string(len_raw as i64));

    
    BytesIO::iter_utf8_lines(blob_raw)
}

#[test]
fn test_unzip_parse_utf8() {
    use cross_rs::memory::bytes_io::BytesIO;
    // 2024-0104
    let file_sqlite = "X:/data/stock/static/202301/20230104.sqlite";

    let 文件夹 = "szftp";
    let 文件名 = "securities_20230104.xml";
    // let 文件夹 = "shse";
    // let 文件名 = "cpxx02020104.txt";

    let blob_raw = match Row_按日期存放文件::get_blob_raw(file_sqlite, 文件夹, 文件名) {
        Ok(v) => v,
        Err(e) => panic!("{e:?}"),
    };
    let len_raw = blob_raw.len();
    let lines = BytesIO::read_utf8_lines(blob_raw).unwrap();
    println!("原始文件: {len_raw} 字节; {} 行字符串(utf-8)", lines.len());
    for i in 0..100 {
        println!("{}", lines[i]);
    }
}

#[test]
fn test_split_blob() {
    for sz in vec![900, 1799, 1800, 2000] {
        let blob = vec![0_u8; sz];
        let list_blob = Row_按日期存放文件::split_mb900(blob);
        println!("===== big blob: {sz} bytes, split to {} parts:", list_blob.len());
        for blob in list_blob {
            print!("\t{} +", blob.len());
        }
        println!();
    }
}

#[test]
fn test_unzip_parse_gbk() {
    // use crate::zip::xzip;
    use cross_rs::memory::bytes_io::BytesIO;
    // 2024-0104
    let file_sqlite = "X:/data/stock/static/202301/20230104.sqlite";

    let 文件夹 = "shse";
    let 文件名 = "cpxx02020104.txt";
    let blob_raw = match Row_按日期存放文件::get_blob_raw(file_sqlite, 文件夹, 文件名) {
        Ok(v) => v,
        Err(e) => panic!("{e:?}"),
    };
    let len_raw = blob_raw.len();
    let lines = BytesIO::read_lines(blob_raw, encoding_rs::GBK).unwrap();
    println!("原始文件: {len_raw} 字节; {} 行字符串(gbk)", lines.len());
    for i in 0..100 {
        println!("{}", lines[i]);
    }
}

#[test]
fn test_StockTick_csv() {
    // temp_StockTick_csv().unwrap();
}